﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Resources;
using System.Collections;
using System.Runtime.InteropServices;
using System.IO;
using System.Configuration;
using System.Data.SqlClient;

namespace QLXMDataAccessLayer.Logic
{
    public class MySqlLogicBase
    {
        private MySqlConnectionStringBuilder connBuilder = null;
        private AppSettingsReader configurationAppSettings = null;
        private MySqlConnection conn = null;
        private MySqlTransaction trans = null;
        private DataTable dt = null;
        private MySqlDataAdapter adapter = null;
        private MySqlCommand cmd = null;
        private string prepareSqlString = "";

        public MySqlConnection OpenConnection()
        {
            if (this.conn == null)
            {
                this.conn = connectDB();
            }
            if (this.conn.State == ConnectionState.Closed)
            {
                this.conn.Open();
            }
            return this.conn;
        }

        public void CloseConnection()
        {
            if (this.conn != null)
            {
                this.conn.Close();
                this.conn.Dispose();
                this.conn = null;
            }
            if (this.adapter != null)
            {
                this.adapter.Dispose();
                this.adapter = null;
            }
            if (this.trans != null)
            {
                this.trans.Dispose();
                this.trans = null;
            }
        }
        public void BeginTransaction()
        {
            if (this.conn != null && this.conn.State == ConnectionState.Open)
            {
                this.trans = this.conn.BeginTransaction();
            }
        }
        public void Commit()
        {
            if (this.trans != null)
            {
                this.trans.Commit();
            }
        }

        public void Rollback()
        {
            if (this.trans != null)
            {
                try
                {
                    this.trans.Rollback();
                }
                catch
                {
                }
            }
        }

        public MySqlConnection connectDB()
        {
            try
            {
                // Settings database's information
                string connStr = ConfigurationManager.ConnectionStrings["qltcdbConnectionString"].ConnectionString;
                //string connStr = "server=localhost;User Id=root;password=;Persist Security Info=True;database=qltcdb;Allow Zero Datetime=true;CharSet=utf8";
                //string connStr = "server=inngroup.dyndns-server.com;User Id=a;password=a;Persist Security Info=True;database=qltcdb;Allow Zero Datetime=true;CharSet=utf8";
                //string connStr = "server=localhost;User Id=root;password=;Persist Security Info=True;database=qltc_import_test;Allow Zero Datetime=true;CharSet=utf8";
                return new MySqlConnection(connStr);  
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {

            }
        }
        public DataTable selectData(string sql)
        {
            dt = new DataTable();
            try
            {
                this.adapter = new MySqlDataAdapter(sql, this.conn);
                this.adapter.Fill(dt);

                return dt;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                dt.Dispose();
            }
        }
        

        /// <summary>
        /// SELECT STATEMENT QUERY TABLES
        /// </summary>
        /// <param name="table">TABLE NAME</param>
        /// <param name="getColumns">COLLECTIONS COLUMN RETURN</param>
        /// <param name="htbKeys">COLLOECTIONS BY PARAMETERS ASSIGNED</param>
        /// <returns></returns>
        public DataTable selectDataSQLQuery(string sql, Hashtable htbKeys)
        {
            DataType dataType = new DataType();
            string query = sql;
         
            cmd = new MySqlCommand();
            this.adapter = new MySqlDataAdapter();
            try
            {
                if (!sql.Contains("WHERE"))
                {
                    query += " WHERE 1 = 1 ";
                }
                
                foreach (DictionaryEntry item in htbKeys)
                {
                    if (item.Value.Equals("null"))
                        query += " AND " + item.Key + " IS ?key" + item.Key;
                    else if (item.Value.ToString().ToUpper().Contains("%"))
                        query += " AND " + item.Key + " LIKE ?key" + item.Key;
                    else
                        query += " AND " + item.Key + " = ?key" + item.Key;
                }

                cmd = new MySqlCommand(query);
                cmd.CommandTimeout = 36000;
                adapter.SelectCommand = cmd;

                foreach (DictionaryEntry item in htbKeys)
                {
                    adapter.SelectCommand.Parameters.Add("?key" + item.Key, MySqlDbType.String);
                    adapter.SelectCommand.Parameters["?key" + item.Key].Value = item.Value;
                }

                adapter.SelectCommand.Connection = this.conn;
                adapter.SelectCommand.Connection.Open();
                DataTable data = new DataTable();
                adapter.Fill(data);
                return data;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                if (dt!=null)
                    dt.Dispose();
            }
        }
        public void UpdateTable(DataTable table, string tblName, string columName)
        {
            if (this.adapter != null)
            {
                MySqlCommand updateCmd = new MySqlCommand();
                MySqlCommand insertCmd = new MySqlCommand();
                MySqlCommand deleteCmd = new MySqlCommand();

                MySqlParameter param;
                updateCmd.CommandType = CommandType.Text;
                insertCmd.CommandType = CommandType.Text;
                insertCmd.CommandType = CommandType.Text;

                StringBuilder updateSqlSb = new StringBuilder();

                StringBuilder insertSqlSb = new StringBuilder();
                StringBuilder insertValueSqlSb = new StringBuilder();

                StringBuilder deleteSqlSb = new StringBuilder();

                updateSqlSb.Append("update " + tblName + " set ");
                insertSqlSb.Append("insert into " + tblName + " (");
                deleteSqlSb.Append("delete from " + tblName + " where " + columName + "=@old" + columName);
                foreach (DataColumn col in table.Columns)
                {
                    param = new MySqlParameter("@" + col.ColumnName, MySqlDbType.VarChar, 50, col.ColumnName);

                    //update query
                    updateSqlSb.Append(col.ColumnName + "=@" + col.ColumnName + ",");
                    //if (!col.ColumnName.Equals(columName))
                    //{
                    //    updateSqlSb.Append(col.ColumnName + "=@" + col.ColumnName + ",");
                    //}
                    //else
                    //{
                    //    param.SourceVersion = DataRowVersion.Original;

                    //}

                    //insert query
                    insertSqlSb.Append(col.ColumnName + ",");
                    insertValueSqlSb.Append("@" + col.ColumnName + ",");

                    updateCmd.Parameters.Add(param);
                    insertCmd.Parameters.Add(param);
                    deleteCmd.Parameters.Add(param);
                }
                //remove "," at the last param
                updateSqlSb.Remove(updateSqlSb.Length-1, 1);
                updateSqlSb.Append(" where " + columName + "=@old" + columName);
                
                param = new MySqlParameter("@old" + columName, MySqlDbType.VarChar, 50, columName);
                param.SourceVersion = DataRowVersion.Original;
                updateCmd.Parameters.Add(param);
                deleteCmd.Parameters.Add(param);

                insertSqlSb.Remove(insertSqlSb.Length - 1, 1);
                insertSqlSb.Append(") values (");
                insertValueSqlSb.Remove(insertValueSqlSb.Length - 1, 1);
                insertValueSqlSb.Append(")");
                insertSqlSb.Append(insertValueSqlSb);
                
                updateCmd.CommandText = updateSqlSb.ToString();
                insertCmd.CommandText = insertSqlSb.ToString();
                deleteCmd.CommandText = deleteSqlSb.ToString();

                updateCmd.Connection = this.conn;
                insertCmd.Connection = this.conn;
                deleteCmd.Connection = this.conn;

                this.adapter.UpdateCommand = updateCmd;
                this.adapter.InsertCommand = insertCmd;
                this.adapter.DeleteCommand = deleteCmd;
                this.adapter.Update(table);
            }
        }
        /// <summary>
        /// SELECT STATEMENT QUERY TABLES
        /// </summary>
        /// <param name="table">TABLE NAME</param>
        /// <param name="getColumns">COLLECTIONS COLUMN RETURN</param>
        /// <param name="htbKeys">COLLOECTIONS BY PARAMETERS ASSIGNED</param>
        /// <returns></returns>
        //public DataTable selectData(string table, Hashtable htbKeys, string getColumns)
        //{
        //    DataType dataType = new DataType();
        //    string sql;
        //    dt = new DataTable(table);
        //    cmd = new MySqlCommand();
        //    adapter = new MySqlDataAdapter();
        //    try
        //    {
        //        sql = "SELECT " + getColumns + " FROM " + table + " WHERE 1 = 1 ";

        //        foreach (DictionaryEntry item in htbKeys)
        //        {
        //            if (item.Value.Equals("null"))
        //                sql += " AND " + item.Key + " IS ?key" + item.Key;
        //            else if (item.Value.ToString().ToUpper().Contains("%"))
        //                sql += " AND " + item.Key + " LIKE ?key" + item.Key;
        //            else
        //                sql += " AND " + item.Key + " = ?key" + item.Key;
        //        }

        //        cmd = new MySqlCommand(sql);
        //        adapter.SelectCommand = cmd;

        //        foreach (DictionaryEntry item in htbKeys)
        //        {
        //            adapter.SelectCommand.Parameters.Add("?key" + item.Key, dataType.GetDataType(table, item.Value.ToString()));
        //            adapter.SelectCommand.Parameters["?key" + item.Key].Value = item.Value;
        //        }

        //        adapter.SelectCommand.Connection = connectDB();
        //        adapter.SelectCommand.Connection.Open();
        //        DataTable data = new DataTable();
        //        adapter.Fill(data);
        //        return data;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        adapter.Dispose();
        //        cmd.Dispose();
        //        dt.Dispose();
        //    }
        //}
        //public DataTable selectData(string table, Hashtable htbKeys, string getColumns, string orderColumns)
        //{
        //    dataType = new DataType();
        //    adapter = new MySqlDataAdapter();
        //    dt = new DataTable();
        //    string sql;

        //    try
        //    {
        //        sql = "SELECT " + getColumns + " FROM " + table + " WHERE 1 = 1 ";

        //        foreach (DictionaryEntry item in htbKeys)
        //        {
        //            if (item.Value.Equals("null"))
        //                sql += " AND " + item.Key + " IS ?key" + item.Key;
        //            else if (item.Value.ToString().ToUpper().Contains("%"))
        //                sql += " AND " + item.Key + " LIKE ?key" + item.Key;
        //            else
        //                sql += " AND " + item.Key + " = ?key" + item.Key;
        //        }

        //        if (Common.isNullOrEmpty(orderColumns.Trim()) == false)
        //        {
        //            sql += " ORDER BY " + orderColumns;
        //        }

        //        cmd = new MySqlCommand(sql);
        //        adapter.SelectCommand = cmd;

        //        foreach (DictionaryEntry item in htbKeys)
        //        {
        //            adapter.SelectCommand.Parameters.Add("?key" + item.Key, dataType.GetDataType(table, item.Value.ToString()));
        //            adapter.SelectCommand.Parameters["?key" + item.Key].Value = item.Value;
        //        }

        //        adapter.SelectCommand.Connection = connectDB();
        //        adapter.SelectCommand.Connection.Open();
        //        adapter.Fill(dt);
        //        return dt;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        adapter.Dispose();
        //        dt.Dispose();
        //        cmd.Dispose();
        //    }
        //}
        public bool insertData(string sql)
        {
            MySqlCommand insertCmd = new MySqlCommand(sql);
            try
            {
                insertCmd.CommandTimeout = 3600;
                insertCmd.Connection = this.conn;

                if (insertCmd.ExecuteNonQuery() > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                insertCmd.Dispose();
            }
            return false;
        }
        //public bool insertData(string table, Hashtable htbValues)
        //{
        //    dataType = new DataType();
        //    cmd = new MySqlCommand();
        //    dt = new DataTable();
        //    string sql = "";
        //    string fields = "";
        //    string paras = "";
        //    try
        //    {
        //        foreach (DictionaryEntry item in htbValues)
        //        {
        //            fields += item.Key + ",";
        //            paras += "?value" + item.Key + ",";
        //        }

        //        fields = fields.Substring(0, fields.Length - 1);
        //        paras = paras.Substring(0, paras.Length - 1);

        //        sql = "INSERT INTO " + table + "(" + fields + ") VALUES (" + paras + ")";

        //        cmd = new MySqlCommand(sql);

        //        foreach (DictionaryEntry item in htbValues)
        //        {
        //            cmd.Parameters.Add("?value" + item.Key, dataType.GetDataType(table, item.Value.ToString()));
        //            cmd.Parameters["?value" + item.Key].Value = item.Value;
        //        }

        //        cmd.Connection = connectDB();
        //        cmd.Connection.Open();

        //        if (cmd.ExecuteNonQuery() > 0)
        //            return true;
        //        return false;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        cmd.Dispose();
        //    }
        //}
        public bool updateData(string sql)
        {
            MySqlCommand updateCmd = new MySqlCommand();
            try
            {
                updateCmd = new MySqlCommand(sql);
                updateCmd.CommandTimeout = 36000;
                updateCmd.Connection = this.conn;

                if (updateCmd.ExecuteNonQuery() > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                updateCmd.Dispose();
            }
            return false;
        }
        //public bool updateData(string table, Hashtable htbValues, Hashtable htbKeys)
        //{
        //    dataType = new DataType();
        //    cmd = new MySqlCommand();
        //    dt = new DataTable();
        //    string sql = "";
        //    string values = "";
        //    string where = "1 = 1";
        //    try
        //    {
        //        foreach (DictionaryEntry item in htbValues)
        //        {
        //            values += item.Key + "=?value" + item.Key + ",";
        //        }
        //        values = values.Substring(0, values.Length - 1);

        //        foreach (DictionaryEntry item in htbKeys)
        //        {
        //            if (!item.Value.Equals("null"))
        //                where += " AND " + item.Key + " = ?key" + item.Key;
        //            else
        //                where += " AND " + item.Key + " is ?key" + item.Key;
        //        }

        //        sql = "UPDATE " + table + " SET \n" + values + " WHERE " + where;

        //        cmd = new MySqlCommand(sql);

        //        foreach (DictionaryEntry item in htbValues)
        //        {
        //            cmd.Parameters.Add("?value" + item.Key, dataType.GetDataType(table, item.Value.ToString()));
        //            cmd.Parameters["?value" + item.Key].Value = item.Value;
        //        }

        //        foreach (DictionaryEntry item in htbKeys)
        //        {
        //            if ("System.Byte[]".Equals(item.Value.ToString()))
        //            {
        //                Byte[] araray = (Byte[])item.Value;
        //                cmd.Parameters.Add(new MySqlParameter("?key" + item.Key, MySqlDbType.Blob));
        //                cmd.Parameters["?key" + item.Key].Value = araray; 

        //            }
        //            else
        //            {
        //                cmd.Parameters.Add("?key" + item.Key, dataType.GetDataType(table, item.Value.ToString()));
        //                cmd.Parameters["?key" + item.Key].Value = item.Value;
        //            }
                    
        //        }

        //        cmd.Connection = connectDB();
        //        cmd.Connection.Open();

        //        if (cmd.ExecuteNonQuery() > 0)
        //            return true;
        //        return false;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        dt.Dispose();
        //        cmd.Dispose();
        //    }
        //}
        public bool deleteData(string sql)
        {
            MySqlCommand delCmd = new MySqlCommand(sql);
            try
            {
                delCmd.Connection = this.conn;
                delCmd.CommandTimeout = 3600;
                if (delCmd.ExecuteNonQuery() > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //dt.Dispose();
                delCmd.Dispose();
            }
        }
        //public bool deleteData(string table, Hashtable htbKeys)
        //{
        //    dataType = new DataType();
        //    cmd = new MySqlCommand();
        //    dt = new DataTable();
        //    string sql = "";
        //    string where = "1 = 1";
        //    try
        //    {
        //        foreach (DictionaryEntry item in htbKeys)
        //        {
        //            if (!item.Value.Equals("null"))
        //                where += " AND " + item.Key + " = ?key" + item.Key;
        //            else
        //                where += " AND " + item.Key + " is ?key" + item.Key;
        //        }

        //        sql = "DELETE FROM " + table + " WHERE " + where;
        //        cmd = new MySqlCommand(sql);
        //        foreach (DictionaryEntry item in htbKeys)
        //        {
        //            cmd.Parameters.Add("?key" + item.Key, dataType.GetDataType(table, item.Value.ToString()));
        //            cmd.Parameters["?key" + item.Key].Value = item.Value;
        //        }

        //        cmd.Connection = connectDB();
        //        cmd.Connection.Open();

        //        if (cmd.ExecuteNonQuery() > 0)
        //            return true;
        //        return false;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        dt.Dispose();
        //        cmd.Dispose();
        //    }
        //}
        public void CallStored(string storeName, Hashtable htbKeys)
        {
            try
            {
                cmd = new MySqlCommand(storeName, this.conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 3600;
                foreach (DictionaryEntry item in htbKeys)
                {
                    cmd.Parameters.Add(item.Key.ToString(), MySqlDbType.String);
                    cmd.Parameters[item.Key.ToString()].Value = item.Value;
                    cmd.Parameters[item.Key.ToString()].Direction = ParameterDirection.Input;
                }

                cmd.ExecuteNonQuery();

            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
            }
        }
        public int CallStoredReturnValue(string storeName, Hashtable htbKeys)
        {
            try
            {
                cmd = new MySqlCommand(storeName, this.conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 3600;
                foreach (DictionaryEntry item in htbKeys)
                {
                    cmd.Parameters.Add(item.Key.ToString(), MySqlDbType.String);
                    cmd.Parameters[item.Key.ToString()].Value = item.Value;
                    cmd.Parameters[item.Key.ToString()].Direction = ParameterDirection.Input;
                }
                cmd.Parameters.Add(new MySqlParameter("?lastinsertid", MySqlDbType.Int32));
                cmd.Parameters["?lastinsertid"].Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                return (Int32)cmd.Parameters["?lastinsertid"].Value;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
            }
        }
        public DataTable selectStore(string storeName)
        {
            dt = new DataTable();
            MySqlCommand storeCmd = new MySqlCommand(storeName, this.conn);
            try
            {
                DataTable allData = new DataTable();
                storeCmd.CommandTimeout = 36000;
                storeCmd.CommandType = CommandType.StoredProcedure;
                //cmd.Connection.Open();

                adapter = new MySqlDataAdapter(storeCmd);
                adapter.Fill(dt);
                
                return dt;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                dt.Dispose();
                storeCmd.Dispose();
            }
        }

        public DataTable selectStore(string storeName, Hashtable htbKeys)
        {
            DataType dataType = new DataType();
            dt = new DataTable();
            try
            {
                DataTable allData = new DataTable();
                MySqlCommand storeCmd = new MySqlCommand(storeName, this.conn);
                storeCmd.CommandTimeout = 36000;
                storeCmd.CommandType = CommandType.StoredProcedure;

                adapter = new MySqlDataAdapter();
                foreach (DictionaryEntry item in htbKeys)
                {
                    storeCmd.Parameters.Add(item.Key.ToString(), MySqlDbType.String);
                    storeCmd.Parameters[item.Key.ToString()].Value = item.Value;
                    storeCmd.Parameters[item.Key.ToString()].Direction = ParameterDirection.Input;
                }
                adapter.SelectCommand = storeCmd;
                adapter.Fill(dt);

                return dt;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                dt.Dispose();
            }
        }
        public DataTable selectStore(String storeName, List<ParamSQL> listParam)
        {
            DataType dataType = new DataType();
            //conn = new MySqlConnection();
            dt = new DataTable();
            try
            {
                DataTable allData = new DataTable();
                //conn = connectDB();
                //conn.Open();
                cmd = new MySqlCommand(storeName, conn);
                cmd.CommandTimeout = 36000;
                cmd.CommandType = CommandType.StoredProcedure;

                adapter = new MySqlDataAdapter();
                foreach (ParamSQL param in listParam)
                {
                    String name = param.Name;
                    String value = param.Value;
                    if ("".Equals(value) && param.Type != DataType.STRING)
                    {
                        value = null;
                    }
                    switch (param.Type)
                    {
                        case DataType.DATE:
                            cmd.Parameters.Add(name, MySqlDbType.Date);
                            break;
                        case DataType.INT:
                            cmd.Parameters.Add(name, MySqlDbType.Int32);
                            break;
                        case DataType.DOUBLE:
                            cmd.Parameters.Add(name, MySqlDbType.Double);
                            break;
                        case DataType.STRING:
                        default:
                            cmd.Parameters.Add(name, MySqlDbType.String);
                            break;
                    }
                    cmd.Parameters[name.ToString()].Value = value;
                    cmd.Parameters[name.ToString()].Direction = ParameterDirection.Input;
                }
                adapter.SelectCommand = cmd;
                adapter.Fill(dt);

                //if (cnn.State == ConnectionState.Open)
                //    cnn.Close();

                return dt;
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                //cnn.Dispose();
                dt.Dispose();
                adapter.Dispose();
            }
        }
        public bool Insert(string table, Hashtable data)
        {
            string sql = "insert into " + table + " ";
            int size = data.Count;
            int cur = 0;
            string fields = "( ";
            string values = "values( ";
            foreach (DictionaryEntry de in data)
            {
                fields += de.Key + ((cur < size-1)?", ": " ");
                values += de.Value + ((cur < size - 1) ? ", " : " ");
                cur++;
            }
            fields += ") ";
            values += ") ";

            sql += fields;
            sql += values;

            cmd = new MySqlCommand();
            try
            {
                cmd = new MySqlCommand(sql);
                cmd.Connection = this.conn;

                if (cmd.ExecuteNonQuery() > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
            }

        }
        //insert
        public MySqlCommand PrepareInsertStatement(string table, Hashtable data)
        {
            MySqlCommand cmd = null;
            try
            {
                prepareSqlString = "insert into " + table;
                string fields = " (";
                string values = "values(";

                bool isFirst = true;

                cmd = this.conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 3600;

                foreach (DictionaryEntry de in data)
                {
                    if (isFirst)
                    {
                        fields += de.Key;
                        values += "@" + de.Key;
                        isFirst = false;
                    }
                    else
                    {
                        fields += ", " + de.Key;
                        values += ", @" + de.Key;
                    }
                    cmd.Parameters.Add("@" + de.Key.ToString(), MySqlDbType.String);
                }
                fields += ") ";
                values += ") ";

                prepareSqlString += fields;
                prepareSqlString += values;

                cmd.CommandText = prepareSqlString;
                cmd.Prepare();
                return cmd;
            }
            catch(Exception ex)
            {
                throw ex;
            }
            return null;
        }

        public bool ExecuteInsert(MySqlCommand cmd, Hashtable data)
        {
            foreach (DictionaryEntry de in data)
            {
                cmd.Parameters["@" + de.Key.ToString()].Value = de.Value;
            }

            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //update
        public MySqlCommand PrepareUpdateStatement(string table, Hashtable data, Hashtable cond)
        {
            MySqlCommand command = null;
            try
            {
                prepareSqlString = "update " + table + " ";
                string fields = "set  ";
                bool isFirst = true;

                command = new MySqlCommand();
                command.Connection = this.conn;
                command.CommandType = CommandType.Text;
                command.CommandTimeout = 3600;

                foreach (DictionaryEntry de in data)
                {
                    if (isFirst)
                    {
                        fields += de.Key + "=@" + de.Key;
                        isFirst = false;
                    }
                    else
                    {
                        fields += ", " + de.Key + "=@" + de.Key;
                    }
                    command.Parameters.Add("@" + de.Key.ToString(), MySqlDbType.String);
                }

                isFirst = true;
                string condStr = " where ";
                foreach (DictionaryEntry de in cond)
                {
                    if (isFirst)
                    {
                        condStr += de.Key + "=@Cond" + de.Key;
                        isFirst = false;
                    }
                    else
                    {
                        condStr += " and " + de.Key + "=@Cond" + de.Key;
                    }
                    command.Parameters.Add("@Cond" + de.Key.ToString(), MySqlDbType.String);
                }

                prepareSqlString += fields;
                prepareSqlString += condStr;

                command.CommandText = prepareSqlString;
                command.Transaction = this.trans;
                command.Prepare();
                return command;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return null;
        }
        public bool ExecuteUpdate(MySqlCommand cmd, Hashtable data, Hashtable condition)
        {
            foreach (DictionaryEntry de in data)
            {
                cmd.Parameters["@" + de.Key.ToString()].Value = de.Value;
            }
            foreach (DictionaryEntry de in condition)
            {
                cmd.Parameters["@Cond" + de.Key.ToString()].Value = de.Value;
            }

            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        //delete
        public MySqlCommand PrepareDeleteStatement(string table, Hashtable cond)
        {
            MySqlCommand command = null;
            try
            {
                prepareSqlString = "delete from " + table;
                bool isFirst = true;

                command = new MySqlCommand();
                command.Connection = this.conn;
                command.CommandType = CommandType.Text;
                command.CommandTimeout = 3600;

                string condStr = " where ";
                foreach (DictionaryEntry de in cond)
                {
                    if (isFirst)
                    {
                        condStr += de.Key + "=@Cond" + de.Key;
                        isFirst = false;
                    }
                    else
                    {
                        condStr += " and " + de.Key + "=@Cond" + de.Key;
                    }
                    command.Parameters.Add("@Cond" + de.Key.ToString(), MySqlDbType.String);
                }

                prepareSqlString += condStr;

                command.CommandText = prepareSqlString;
                command.Transaction = this.trans;
                command.Prepare();
                return command;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return null;
        }
        public bool ExecuteDelete(MySqlCommand cmd, Hashtable condition)
        {
            foreach (DictionaryEntry de in condition)
            {
                cmd.Parameters["@Cond" + de.Key.ToString()].Value = de.Value;
            }

            try
            {
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
        //support and condition
        public bool Update(string table, Hashtable data, Hashtable condition)
        {
            string sql = "update " + table + " ";
            int size = data.Count;
            int cur = 0;
            string fields = "set  ";
            foreach (DictionaryEntry de in data)
            {
                fields += de.Key + "=" + de.Value + ((cur < size - 1) ? ", " : " ");
                cur++;
            }
            string cond = " where ";
            size = condition.Count;
            cur = 0;
            foreach (DictionaryEntry de in condition)
            {
                cond += de.Key + "=" + de.Value + ((cur < size - 1) ? " and  " : " ");
                cur++;
            }

            sql += fields;
            sql += cond;

            cmd = new MySqlCommand();
            try
            {
                cmd = new MySqlCommand(sql);
                cmd.CommandTimeout = 36000;
                cmd.Connection = this.conn;

                if (cmd.ExecuteNonQuery() > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
            }
        }
        public void Delete(string table, Hashtable condition)
        { 
        
        }

        public String Insert(String sqlInsert, String sqlValue)
        {
            IDbConnection idbConn =  this.OpenConnection();
            int initTime = 0;
            try
            {
                IDbCommand idbCommand = idbConn.CreateCommand();
                initTime = Environment.TickCount;

                IDbTransaction idbtransaction = idbConn.BeginTransaction(IsolationLevel.RepeatableRead);
                idbCommand.Transaction = idbtransaction;
                StringBuilder builder = new StringBuilder();
                builder.Append(string.Format(sqlInsert + sqlValue));
                idbCommand.CommandText = builder.ToString();
                idbCommand.ExecuteNonQuery();
                idbtransaction.Commit();
            }
            finally
            {
                idbConn.Close();
                conn.Close();
            }
            return "{0} ms"  + (Environment.TickCount - initTime).ToString();
        }

    }
}
